﻿
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data.OracleClient;
using System.Collections.Generic;


namespace DataAccess.Oracle
{

    /**

     * 写程序之前

     * 首先引用System.Data.OracleClient

     */

    public class OracleHelper
    {

        #region 变量

        /// <summary>

        /// 数据库连接对象

        /// </summary>

        private static OracleConnection _con = null;
        public string strConnectionString;
        public string strDataProviderName;
        public static string constr = "";

        #endregion


        #region 属性

        /// <summary>

        /// 获取或设置数据库连接对象

        /// </summary>

        public static OracleConnection Con
        {

            get
            {

                if (OracleHelper._con == null)
                {

                    OracleHelper._con = new OracleConnection();

                }

                if (OracleHelper._con.ConnectionString == "")
                {
                    initConnString();
                    OracleHelper._con.ConnectionString = OracleHelper.constr;

                }

                return OracleHelper._con;

            }

            set
            {

                OracleHelper._con = value;

            }

        }

        #endregion


        #region 方法

        private static void initConnString()
        {
            constr = GetConnectionSTRByName(ConfigurationManager.AppSettings["DefaultConnectionStringName"]);
            // strDataProviderName = GetProviderNameByName(ConfigurationManager.AppSettings["DefaultConnectionStringName"]);
        }
        private static string GetConnectionSTRByName(string dsn)
        {
            return ConfigurationManager.ConnectionStrings[dsn].ConnectionString;
        }

        /**/
        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }

        //private string GetProviderNameByName(string dsn)
        //{
        //    return ConfigurationManager.ConnectionStrings[dsn].ProviderName;
        //}


        #region 执行返回一行一列的数据库操作

        /// <summary>

        /// 执行返回一行一列的数据库操作

        /// </summary>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>第一行第一列的记录</returns>

        public static object ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            object result = null;
           
            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;
                        if (param != null) cmd.Parameters.AddRange(param);
                        if (OracleHelper.Con.State != ConnectionState.Open)
                        {
                            OracleHelper.Con.Open();
                        }
                        result = cmd.ExecuteScalar();

                    }

                    catch (Exception ex)
                    {

                        result = null;

                    }

                }


            }

            return result;

        }

        #endregion


        #region 执行不查询的数据库操作

        /// <summary>

        /// 执行不查询的数据库操作

        /// </summary>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>受影响的行数</returns>

        public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            int result = 0;

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;

                        if (param != null) cmd.Parameters.AddRange(param);

                        OracleHelper.Con.Open();

                        result = cmd.ExecuteNonQuery();

                    }

                    catch (Exception ex)
                    {

                        result = 0;

                    }

                }


            }

            return result;

        }



        public static int ExecuteNonQuery(OracleTransaction transaction, string commandText, CommandType commandType, params OracleParameter[] param)
        {

            int result = 0;
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rollbacked    or commited, please    provide    an open    transaction.", "transaction");

            // Create a    command    and    prepare    it for execution
            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, param);
            result = cmd.ExecuteNonQuery();
            return result;

        }





        public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rollbacked    or commited, please    provide    an open    transaction.", "transaction");

            // Create a    command    and    prepare    it for execution
            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            // Execute the command & return    the    results
            object retval = cmd.ExecuteScalar();

            // Detach the SqlParameters    from the command object, so    they can be    used again
            cmd.Parameters.Clear();
            return retval;
        }





        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static bool ExecuteSqlTran(List<string> SQLStringList)
        {
            bool re = false;
            using (OracleConnection connection = OracleHelper.Con)
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    re = true;
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    re = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return re;

        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static bool ExecuteSqlTran(Dictionary<string, OracleParameter[]> sqlDic)
        {
            bool re = false;
            using (OracleConnection connection = OracleHelper.Con)
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (var item in sqlDic)
                    {

                        cmd.CommandText = item.Key;
                        cmd.Parameters.Clear();
                        if (item.Value != null) cmd.Parameters.AddRange(item.Value);
                        cmd.ExecuteNonQuery();


                    }
                    tx.Commit();
                    re = true;
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    re = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return re;

        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static bool ExecuteSqlTran(List<Dictionary<string, OracleParameter[]>> sqlLisDic)
        {
            bool re = false;
            using (OracleConnection connection = OracleHelper.Con)
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (var item in sqlLisDic)
                    {

                        cmd.CommandText = item.Keys.First<string>();
                        cmd.Parameters.Clear();
                        if (item[item.Keys.First<string>()] != null) cmd.Parameters.AddRange(item[item.Keys.First<string>()]);
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    re = true;
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    re = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return re;

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string SQLString)
        {
            using (OracleConnection connection = OracleHelper.Con)
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }



        #endregion


        #region 执行返回一条记录的泛型对象

        /// <summary>

        /// 执行返回一条记录的泛型对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="reader">只进只读对象</param>

        /// <returns>泛型对象</returns>

        private static T ExecuteDataReader<T>(IDataReader reader)
        {

            T obj = default(T);

            try
            {

                Type type = typeof(T);

                obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象   

                //obj = (T)Assembly.Load(OracleHelper._assemblyName).CreateInstance(OracleHelper._assemblyName + "." + type.Name);//从另一个程序集里面通过反射的方式创建指定类型的对象 

                PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性

                foreach (PropertyInfo propertyInfo in propertyInfos)
                {

                    for (int i = 0; i < reader.FieldCount; i++)
                    {

                        string fieldName = reader.GetName(i);

                        if (fieldName.ToLower() == propertyInfo.Name.ToLower())
                        {

                            object val = reader[propertyInfo.Name];//读取表中某一条记录里面的某一列信息

                            if (val != null && val != DBNull.Value)

                                propertyInfo.SetValue(obj, val, null);//给对象的某一个属性赋值

                            break;

                        }

                    }

                }

            }

            catch (Exception ex)
            {

            }

            return obj;

        }

        #endregion


        #region 执行返回一条记录的泛型对象

        /// <summary>

        /// 执行返回一条记录的泛型对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>实体对象</returns>

        public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            T obj = default(T);

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    cmd.CommandType = commandType;

                    cmd.Parameters.AddRange(param);

                    OracleHelper.Con.Open();

                    OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reader.Read())
                    {

                        obj = OracleHelper.ExecuteDataReader<T>(reader);

                    }

                }

            }

            return obj;

        }

        #endregion


        #region 执行返回多条记录的泛型集合对象

        /// <summary>

        /// 执行返回多条记录的泛型集合对象

        /// </summary>

        /// <typeparam name="T">泛型类型</typeparam>

        /// <param name="commandText">Oracle语句或存储过程名</param>

        /// <param name="commandType">Oracle命令类型</param>

        /// <param name="param">Oracle命令参数数组</param>

        /// <returns>泛型集合对象</returns>

        public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
        {

            List<T> list = new List<T>();

            using (OracleHelper.Con)
            {

                using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
                {

                    try
                    {

                        cmd.CommandType = commandType;

                        cmd.Parameters.AddRange(param);

                        OracleHelper.Con.Open();

                        OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                        while (reader.Read())
                        {


                            T obj = OracleHelper.ExecuteDataReader<T>(reader);

                            list.Add(obj);

                        }

                    }

                    catch (Exception ex)
                    {

                        list = null;

                    }

                }

                return list;

            }
        }
        #endregion

        #endregion



    }
}